MySQL 时间戳（Timestamp）函数

1. MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()mysql> select current_timestamp, current_timestamp();

+———————+———————+| current_timestamp | current_timestamp() |+———————+———————+| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |+———————+———————+

2. MySQL （Unix 时间戳、日期）转换函数：unix_timestamp(),unix_timestamp(date),from_unixtime(unix_timestamp),from_unixtime(unix_timestamp,format)

下面是示例：select unix_timestamp(); – 1218290027select unix_timestamp(‘2008-08-08’); – 1218124800select unix_timestamp(‘2008-08-08 12:30:00’); – 1218169800

select from_unixtime(1218290027); – ‘2008-08-09 21:53:47’select from_unixtime(1218124800); – ‘2008-08-08 00:00:00’select from_unixtime(1218169800); – ‘2008-08-08 12:30:00’

select from_unixtime(1218169800, ‘%Y %D %M %h:%i:%s %x’); – ‘2008 8th August 12:30:00 2008’

3. MySQL 时间戳（timestamp）转换、增、减函数：timestamp(date) – date to timestamptimestamp(dt,time) – dt + timetimestampadd(unit,interval,datetime_expr) –timestampdiff(unit,datetime_expr1,datetime_expr2) –

请看示例部分：select timestamp(‘2008-08-08’); – 2008-08-08 00:00:00select timestamp(‘2008-08-08 08:00:00’, ‘01:01:01’); – 2008-08-08 09:01:01select timestamp(‘2008-08-08 08:00:00’, ‘10 01:01:01’); – 2008-08-18 09:01:01

select timestampadd(day, 1, ‘2008-08-08 08:00:00’); – 2008-08-09 08:00:00select date_add(‘2008-08-08 08:00:00’, interval 1 day); – 2008-08-09 08:00:00

MySQL timestampadd() 函数类似于 date_add()。select timestampdiff(year,’2002-05-01’,’2001-01-01’); – -1select timestampdiff(day ,’2002-05-01’,’2001-01-01’); – -485select timestampdiff(hour,’2008-08-08 12:00:00’,’2008-08-08 00:00:00’); – -12

select datediff(‘2008-08-08 12:00:00’, ‘2008-08-01 00:00:00’); – 7

MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。

TIMESTAMP的变体1，TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP在创建新记录和修改现有记录的时候都对这个数据列刷新

2，TIMESTAMP DEFAULT CURRENT_TIMESTAMP 在创建新记录的时候把这个字段设置为当前时间，但以后修改时，不再刷新它

3，TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 在创建新记录的时候把这个字段设置为0，以后修改时刷新它